package com.example.demo.repository;

import com.example.demo.entity.SchoolStudent;
import org.springframework.data.r2dbc.repository.Modifying;
import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.r2dbc.repository.R2dbcRepository;
import org.springframework.data.repository.query.Param;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

public interface SchoolStudentRepository extends R2dbcRepository<SchoolStudent, Long> {

    @Query("SELECT * FROM t_student WHERE school_class =:class_id order by id")
    Flux<SchoolStudent> queryAllBySchoolClass(Long class_id);

    @Query("SELECT * FROM t_student WHERE school_class IN (SELECT  id from t_class WHERE grade =:grade_id) order by id")
    Flux<SchoolStudent> queryAllBySchoolGrade(Long grade_id);

    @Query("delete FROM t_student WHERE school_class = :class_id")
    Mono<Void> deleteByClassId(Long class_id);

    @Modifying
    @Query( "insert into t_student(id,name,sex,age,school_class) VALUES (:#{#schoolStudent.id},:#{#schoolStudent.name},:#{#schoolStudent.sex},:#{#schoolStudent.age},:#{#schoolStudent.schoolClass})"+
            " on conflict (id) do update SET " +
            "name = CASE WHEN :#{#schoolStudent.name} IS NULL THEN t_student.name ELSE :#{#schoolStudent.name} END, " +
            "sex = CASE WHEN :#{#schoolStudent.sex} IS NULL THEN t_student.sex ELSE :#{#schoolStudent.sex} END, " +
            "age = CASE WHEN :#{#schoolStudent.age} IS NULL THEN t_student.age ELSE :#{#schoolStudent.age} END, " +
            "school_class = CASE WHEN :#{#schoolStudent.schoolClass} IS NULL THEN t_student.school_class ELSE :#{#schoolStudent.schoolClass} END " )
    Mono<SchoolStudent> insertOrUpdateStudent(@Param("schoolStudent") SchoolStudent schoolStudent);
}

